﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace NWBA_Public
{
    public class Payee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Postcode { get; set; }
        public string Phone { get; set; }
        public Payee(int id, string name, string address,
            string city, string state, string postcode, string phone)
        {
            this.ID = id;
            this.Name = name;
            this.Address = address;
            this.City = city;
            this.State = state;
            this.Postcode = postcode;
            this.Phone = phone;
        }
    }
    public class PayeeRepository
    {
        //public static SqlConnection con = new SqlConnection(ServerString.getServerString());
        public static SqlConnection con = new SqlConnection(ServerString.getServerString());
        public PayeeRepository()
        {
        }
        public static Payee Select(int id)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(
                    "SELECT PayeeID, PayeeName, Address, City, State, Postcode, Phone" +
                    "FROM [Payee] WHERE PayeeID=" + id, con);
                SqlDataReader reader = command.ExecuteReader();
                Payee temp;
                while (reader.Read())
                {
                    temp = new Payee(reader.GetInt32(0), reader.GetString(1),
                        reader.GetString(2), reader.GetString(3), reader.GetString(4),
                        reader.GetString(5), reader.GetString(6));
                    return temp;
                }
                return null;
            }
            catch (Exception re)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        public static List<string> SelectAllPayeeNames()
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(
                    "SELECT PayeeName FROM [Payee]", con);
                SqlDataReader reader = command.ExecuteReader();
                var temp = new List<string> ();
                while (reader.Read())
                {
                    temp.Add(reader.GetString(0));
                }
                return temp;
            }
            catch (Exception re)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        public static int SelectIDByName(string name)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(
                    "SELECT PayeeID FROM [Payee] WHERE PayeeName='" + name + "'", con);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return reader.GetInt32(0);
                }
                return -2;
            }
            catch (Exception re)
            {
                return -1;
            }
            finally
            {
                con.Close();
            }
        }
        public static string SelectNameByID(int id)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(
                    "SELECT PayeeName FROM [Payee] WHERE PayeeID=" + id, con);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return reader.GetString(0);
                }
                return null;
            }
            catch (Exception re)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
    }
}